<?php
class Report extends Model_Abstract {

	const DAILY = 1;
	const WEEKLY = 2;
	const MONTHLY = 3;
	
	private $_start;
	private $_end;
	private $_interval;

	//get the start and end time, defautl start or end of today
	private function _init($duration, $customrangeDate=array()){
		//Q::loadFile('TimeUtil.php', Helper_Dir);
		switch ($duration){
			// yesterday
			case "1":
				$this->_start = date('Y-m-d H:i:s', TimeUtil::startOfYesterday());
				$this->_end = date('Y-m-d H:i:s', TimeUtil::endOfYesterday());
				break;
				// last 7 days
			case "2":
				$this->_start = date('Y-m-d H:i:s', TimeUtil::startOfLast7Days());
				break;
				// month to date
			case "3":
				$this->_start = date('Y-m-d H:i:s', TimeUtil::startOfMonth());
				break;
				// custom date range
			case "4":
				$this->_start = $customrangeDate['start'];
				$this->_end = date('Y-m-d', strtotime('+1 day',strtotime($customrangeDate['end'])));
				break;
				// lifetime
			case "5":
				break;
				// today
			default:
				$this->_start = date('Y-m-d H:i:s', TimeUtil::startOfToday());
				//$this->_end = TimeUtil::endOfToday();
				break;
		}
	}
	
	private function getDateFormat($interval, $fieldName = 'time_new', $AS_Name = 'date'){
		switch($interval){
			case self::DAILY:
				$dateFormat = "DATE({$fieldName}) ";
				break;
			case self::WEEKLY:
				$dateFormat = "YEARWEEK({$fieldName}) ";
				break;
			case self::MONTHLY:
				$dateFormat = "DATE_FORMAT({$fieldName}, '%Y-%m') ";
				break;
			default:	//Hourly
				$dateFormat = "DATE_FORMAT({$fieldName} , '%Y-%m-%d %H:00:00') ";
				break;
		}
		
		if($AS_Name != ''){
			$dateFormat .= " AS {$AS_Name} ";
		}
		return $dateFormat;
	}

	public function getMediaReport($duration, $customrangeDate=array(), $interval, $dispCol=array()){
		$this->_init($duration, $customrangeDate);
		$cols=array('buy_type', 'media_type', 'pub', 'pla', 'cam', 'cre', 'monetization', 'site');
			
		$dateFormat = $this->getDateFormat($interval);
		$sqlStart = "SELECT {$dateFormat}, ";
			
		$vars = array();
		$query = array();
		if(!empty($this->_start)){
			$query[] = '`time_new`>=? ';
			$vars[] = $this->_start;
		}
		if(!empty($this->_end)){
			$query[] = '`time_new`<?';
			$vars[] = $this->_end;
		}
			
		if(!empty($query)){
			$sqlwhere = ' WHERE ' . implode(' AND ', $query);
		}
		$sqlgroup = ' GROUP BY date ';
		foreach($dispCol as $col){
			$sqlgroup .= (', '.$cols[$col]);
			$sqlStart .= ($cols[$col].', ');
		}
		
			
		$tbNames = $this->getTbByDuration();	dump($tbNames);
		if(!empty($tbNames)){
			$finalResult = array();
			foreach($tbNames as $tbName){
				$sql = $sqlStart . " SUM(impression) AS impression, SUM(click) AS click, SUM(basic_reg) AS leads, SUM(suppress) AS suppress, SUM(conversion) AS conversion, SUM(revenue) AS revenue, SUM(spend) AS cost FROM $tbName " . $sqlwhere . $sqlgroup;
				try{
					$result = $this->_connection->query($sql, $vars);
					$finalResult = array_merge($result, $finalResult);
				}
				catch (Exception $e){
					QLog::log(__CLASS__.' '.__METHOD__.' --- '.$e->getMessage(), QLog::ERR);
					throw new Model_Exception($e->getMessage(), $e->getCode());
				}
			}
			return $finalResult;
		}
		return false;
	}

	public function getMonetizationReport($duration){
			
	}

	public function getSiteReport($duration, $customrangeDate=array(), $interval, $dispCol=array(), $page=1){
		$this->_init($duration, $customrangeDate);
		$cols=array('buy_type', 'media_type', 'pub', 'pla', 'cam', 'cre', 'monetization', 'site');

		$dateFormat = $this->getDateFormat($interval);
		$sqlStart = "SELECT {$dateFormat}, ";
		
		$vars = array();
		$query = array();
		if(!empty($this->_start)){
			$query[] = '`time_new`>=? ';
			$vars[] = $this->_start;
		}
		if(!empty($this->_end)){
			$query[] = '`time_new`<?';
			$vars[] = $this->_end;
		}
		
		if(!empty($query)){
			$sqlwhere = ' WHERE ' . implode(' AND ', $query);
		}
		$sqlgroup = ' GROUP BY date ';
		$groupByCols = array('date');
		foreach($dispCol as $col){
			$sqlgroup .= (', '.$cols[$col]);
			$sqlStart .= ($cols[$col].', ');
			
			$groupByCols[] = $cols[$col];
		}
		$tbNames = $this->getTbByDuration();	dump($tbNames);
		if(!empty($tbNames)){
			$finalResult = array();
			foreach($tbNames as $tbName){
				$sql = $sqlStart . " landing, basic_reg, morecredit AS more_credit, basic_reg_coreg, full_reg, full_reg_coreg, 
									 profiling_coreg_start, profiling_coreg_end, confirmed, confirmed_coreg, daily_coreg, 
									 homeview AS home_view, won, credit, token, free, reward,  played FROM `$tbName` " . $sqlwhere;
				try{
					$result = $this->_connection->query($sql, $vars);
					$sumCols = array('landing', 'basic_reg', 'more_credit', 'basic_reg_coreg', 'full_reg', 'full_reg_coreg',
									 'profiling_coreg_start', 'profiling_coreg_end', 'confirmed', 'confirmed_coreg', 
									 'daily_coreg', 'home_view', 'won', 'credit', 'token', 'free', 'reward', 'played'
									);
					$totalCount = 0;
					if(!empty($result)){
						$result = $this->groupBy_Cols($result, $groupByCols, $sumCols, true);
						$totalCount = count($result);
						if($page != 0){
							$limit = is_null(Q::ini('appini/items_per_page')) ?  20 : Q::ini('appini/items_per_page');
							$result = array_slice($result, ($page-1)*$limit, $limit);
							$result['totalCount'] = $totalCount;
						}
					}
					$finalResult = array_merge($result, $finalResult);
				}
				catch (Exception $e){
					QLog::log(__CLASS__.' '.__METHOD__.' --- '.$e->getMessage(), QLog::ERR);
					throw new Model_Exception($e->getMessage(), $e->getCode());
				}				
			}
			return $finalResult;
		}
		
		return false;
	}

	public function getGameReport($duration, $customrangeDate=array(), $interval, $dispCol=array()){
		$this->_init($duration, $customrangeDate);

		$vars = array();
		$query = array();
		if(!empty($this->_start)){
			$query[] = '`time_new`>=? ';
			$vars[] = $this->_start;
		}
		if(!empty($this->_end)){
			$query[] = '`time_new`<?';
			$vars[] = $this->_end;
		}
		if(!empty($query)){
			$sqlwhere = ' WHERE ' . implode(' AND ', $query);
		}
		$sqlwhere .=  ' AND uid != 0';
		
		$dateFormat = $this->getDateFormat($interval);
		$s_LeftJoinOn_r = " {$this->getDateFormat($interval, 'time_new', '')} = {$this->getDateFormat($interval, 'created_date', '')} ";
		$sqlStart = " SELECT {$dateFormat},";
		$sqlgroup = ' GROUP BY date, site ';		

		$tbNames = $this->getTbByDuration();	dump($tbNames);
		if(!empty($tbNames)){
			$finalResult = array();
			$vars2 = array();
			$query2 = array();
			if(!empty($this->_start)){
				$query2[] = '`data_time`>=? ';
				$vars2[] = $this->_start;
			}
			if(!empty($this->_end)){
				$query2[] = '`data_time`<?';
				$vars2[] = $this->_end;
			}
			
			$where2 = '';
			if(!empty($query2)){
				$where2 = ' WHERE ' . implode(' AND ', $query2);
			}
			$sql = "SELECT SUM(revenue) AS revenue FROM `advertiser_data` " . $where2;
			$offer_income = $this->_connection->query($sql, $vars2);
			foreach($tbNames as $tbName){
				$sql = $sqlStart . " sites.game, site, homeview, SUM(morecredit) AS more_credit, SUM(s.free) AS free, COUNT(s.id) AS user_num, SUM(s.played) AS total_played, SUM(s.won) AS won, SUM(r.credit) AS credit, COUNT(r.id) AS offer_num, SUM(s.token) AS token FROM `$tbName` AS s LEFT JOIN `sites` ON s.site = sites.domain LEFT JOIN `rewards` AS r ON " . $s_LeftJoinOn_r . " AND s.uid=r.user_id " . $sqlwhere . $sqlgroup;
				try{
					$gamereport = $this->_connection->query($sql, $vars);
					$this->formatResulttime($gamereport, $interval);
					
					foreach($gamereport as $k=>$r){
						$gamereport[$k]['offer_income'] = $offer_income[0]['revenue'];
					}
					$finalResult = array_merge($gamereport, $finalResult);
				}
				catch (Exception $e){
					QLog::log(__CLASS__.' '.__METHOD__.' --- '.$e->getMessage(), QLog::ERR);
					throw new Model_Exception($e->getMessage(), $e->getCode());
				}
			}
			return $finalResult;
		}
		return false;
	}

	public function getShareReport($duration, $customrangeDate=array(), $interval, $dispCol=array()){
		$this->_init($duration, $customrangeDate);
		$cols=array('buy_type', 'media_type', 'pub', 'pla', 'cam', 'cre', 'monetization', 'site');

		$vars = array();
		$query = array();
		if(!empty($this->_start)){
			$query[] = '`time_new`>=? ';
			$vars[] = $this->_start;
		}
		if(!empty($this->_end)){
			$query[] = '`time_new`<?';
			$vars[] = $this->_end;
		}
		if(!empty($query)){
			$sqlwhere = ' AND ' . implode(' AND ', $query);
		}
		
		$dateFormat = $this->getDateFormat($interval);
		$sqlStart = " SELECT {$dateFormat},";
		$sqlgroup = ' GROUP BY date  ';
		if(isset($dispCol[7])){
			$sqlStart .= ' site, ';
			$sqlgroup .= ' ,site ';
		}
		
		$tbNames = $this->getTbByDuration();	dump($tbNames);
		if(!empty($tbNames)){
			$finalResult = array();
			foreach($tbNames as $tbName){
				try{
					$sharelist = array();
		
					$sql = $sqlStart . " SUM(share_facebook) AS share_facebook, SUM(ref_facebook) AS ref_facebook, SUM(landing) AS fb_landing FROM `$tbName` WHERE `ref_facebook`>0 ";
					$sql .= $sqlwhere . $sqlgroup;
					$result = $this->_connection->query($sql, $vars);
					foreach($result as $v){
						$sharelist[$v['date'].'|'.$v['site']]['share_facebook'] = $v['share_facebook'];
						$sharelist[$v['date'].'|'.$v['site']]['ref_facebook'] = $v['ref_facebook'];
						$sharelist[$v['date'].'|'.$v['site']]['fb_landing'] = $v['fb_landing'];
					}
		
					$sql = $sqlStart . " SUM(share_twitter) AS share_twitter, SUM(ref_twitter) AS ref_twitter, SUM(landing) AS tw_landing FROM `$tbName` WHERE `ref_twitter`>0 ";
					$sql .= $sqlwhere . $sqlgroup;
					$result = $this->_connection->query($sql, $vars);
					foreach($result as $v){
						$sharelist[$v['date'].'|'.$v['site']]['share_twitter'] = $v['share_twitter'];
						$sharelist[$v['date'].'|'.$v['site']]['ref_twitter'] = $v['ref_twitter'];
						$sharelist[$v['date'].'|'.$v['site']]['tw_landing'] = $v['tw_landing'];
					}
		
					$sql = $sqlStart . " SUM(share_email) AS share_email, SUM(ref_email) AS ref_email, SUM(landing) AS em_landing FROM `$tbName` WHERE `ref_email`>0 ";
					$sql .= $sqlwhere . $sqlgroup;
					$result = $this->_connection->query($sql, $vars);
					foreach($result as $v){
						$sharelist[$v['date'].'|'.$v['site']]['share_email'] = $v['share_email'];
						$sharelist[$v['date'].'|'.$v['site']]['ref_email'] = $v['ref_email'];
						$sharelist[$v['date'].'|'.$v['site']]['em_landing'] = $v['em_landing'];
					}
		
					$sql = $sqlStart . " SUM(basic_reg) AS basic_reg, pub FROM `$tbName` WHERE cam = 'ref' AND (pub='fb' OR pub='tw' OR pub='em') ";
					$sql .= $sqlwhere . $sqlgroup . ' , pub ';
					$result = $this->_connection->query($sql, $vars);
					foreach($result as $v){
						if($v['pub'] == 'fb'){
							$key_basicreg = 'fb_basic_reg';
						}else if($v['pub'] == 'tw'){
							$key_basicreg = 'tw_basic_reg';
						}else if($v['pub'] == 'em'){
							$key_basicreg = 'em_basic_reg';
						}
						$sharelist[$v['date'].'|'.$v['site']][$key_basicreg] = $v['basic_reg'];
					}
		
					$sql = $sqlStart . " SUM(ref_email_open) AS ref_email_open FROM `$tbName` WHERE `ref_email_open`>0 ";
					$sql .= $sqlwhere . $sqlgroup;
					$result = $this->_connection->query($sql, $vars);
					foreach($result as $v){
						$sharelist[$v['date'].'|'.$v['site']]['ref_email_open'] = $v['ref_email_open'];
					}
		
					$sql = $sqlStart . " SUM(ref_email_sent) AS ref_email_sent FROM `$tbName` WHERE `ref_email_sent`>0 ";
					$sql .= $sqlwhere . $sqlgroup;
					$result = $this->_connection->query($sql, $vars);
					foreach($result as $v){
						$sharelist[$v['date'].'|'.$v['site']]['ref_email_sent'] = $v['ref_email_sent'];
					}
					
					$finalResult = array_merge($sharelist, $finalResult);
				}
				catch (Exception $e){
					QLog::log(__CLASS__.' '.__METHOD__.' --- '.$e->getMessage(), QLog::ERR);
					throw new Model_Exception($e->getMessage(), $e->getCode());
				}
			}
			return $finalResult;
		}
		return false;
	}

	public function getAdvertiserReport($duration, $customrangeDate=array(), $interval, $dispCol=array()){
		$this->_init($duration, $customrangeDate);
		$vars = array();
		$query = array();
		if(!empty($this->_start)){
			$query[] = '%1$s>=? ';
			$vars[] = $this->_start;
		}
		if(!empty($this->_end)){
			$query[] = '%1$s<?';
			$vars[] = $this->_end;
		}
		
		if(!empty($query)){
			$sqlwhere = ' WHERE ' . implode(' AND ', $query);
		}
		
		$dateFormat = $this->getDateFormat($interval, '%1$s');

		$tbNames = $this->getTbByDuration();	dump($tbNames);
		if(!empty($tbNames)){
			$finalResult = array();
			foreach($tbNames as $tbName){
				try{
					$adreport = array();
		
					//get site_data relative to coreg, array in $site_data_Array
					$sql = " SELECT {$dateFormat}, site, s.monetization AS advertiser, SUM(s.basic_reg) AS basic_reg, SUM(s.basic_reg_coreg) AS basic_reg_coreg, SUM(s.full_reg) AS full_reg, SUM(s.full_reg_coreg) AS full_reg_coreg, SUM(s.profiling_coreg_start) AS profiling_coreg_start, SUM(s.profiling_coreg_end) AS profiling_coreg_end, SUM(s.confirmed) AS confirmed, SUM(s.confirmed_coreg) AS confirmed_coreg FROM `$tbName` AS s " . $sqlwhere . " AND monetization IN ('Fluent', 'Prospectiv') AND site!='' GROUP BY date, advertiser, site ";
					$sql = str_replace('%1$s', 'time_new', $sql);
					$site_data_Array = $this->_connection->query($sql, $vars);
					
					//get advertiser_data and group by advertiser, time and site      array in $ad_data_Array
					$sql = " SELECT {$dateFormat}, ad.name AS advertiser, sites.domain AS site, SUM(revenue) AS revenue, SUM(click) AS click FROM advertiser_data AS ad_da LEFT JOIN advertisers AS ad ON ad_da.advertiser_id=ad.id LEFT JOIN sites ON ad_da.site_id=sites.id " . $sqlwhere . ' GROUP BY date, advertiser, site ';
					$sql = str_replace('%1$s', 'data_time', $sql);
					$result = $this->_connection->query($sql, $vars);
					$ad_data_Array = Array();
					foreach($result as $ad_da){
						$ad_data_Array[ $ad_da['t'] . ' ' . $ad_da['site'] . ' ' . $ad_da['advertiser'] ] = array('revenue' => $ad_da['revenue'], 'click' => $ad_da['click']);
					}
					
					//get coreg data from array $site_data_Array and array $ad_data_Array
					foreach($site_data_Array as $k=>$site_data){
						$revenue = $ad_data_Array[ $site_data['t'] . ' ' . $site_data['site'] . ' ' . $site_data['advertiser'] ]['revenue'];
						
						$adreport['short coreg'][] = array('placement' => 'short coreg',
														 't' => $site_data['date'],
														 'site' => $site_data['site'],
														 'advertiser' => $site_data['advertiser'],
														 'impression' => $site_data['basic_reg'],
														 'redirect' => $site_data['basic_reg_coreg'],
														 'revenue' => $revenue
														);
						$adreport['long coreg'][] = array('placement' => 'long coreg',
														 't' => $site_data['date'],
														 'site' => $site_data['site'],
														 'advertiser' => $site_data['advertiser'],
														 'impression' => $site_data['full_reg'],
														 'redirect' => $site_data['full_reg_coreg'],
														 'revenue' => $revenue
														);
						$adreport['profile update'][] = array('placement' => 'profile update',
														 't' => $site_data['date'],
														 'site' => $site_data['site'],
														 'advertiser' => $site_data['advertiser'],
														 'impression' => $site_data['profiling_coreg_start'],
														 'redirect' => $site_data['profiling_coreg_end'],
														 'revenue' => $revenue
														);
						$adreport['confirm coreg'][] = array('placement' => 'confirm coreg',
														 't' => $site_data['date'],
														 'site' => $site_data['site'],
														 'advertiser' => $site_data['advertiser'],
														 'impression' => $site_data['confirmed_coreg'],
														 'redirect' => $site_data['confirmed'],
														 'revenue' => $revenue
														);
					}
					
		//			//get site_data relative to lead5media, array in $site_data_lead5media_Array
		//			$sql = " SELECT {$dateFormat}, COUNT(optin_edu) AS conversion FROM users " . $sqlwhere . " AND optin_edu>3 GROUP BY date ";
		//			$sql = str_replace('%1$s', 'optin_edu_date', $sql);
		//			$lead5media_conversion = $this->_connection->query($sql, $vars);
		//			$dateFormat_u = $this->getDateFormat($interval, '%1$s', '');
		//			$dateFormat_s = $this->getDateFormat($interval, '%2$s', '');
		//			$sql = " SELECT {$dateFormat}, site, u.id, SUM(s.basic_reg) AS impression FROM users u LEFT JOIN `site_data` s ON {$dateFormat_s}=$dateFormat_u " . $sqlwhere . " AND site!='' GROUP BY date, site ";
		//			$sql = str_replace(array('%1$s', '%2$s'), array('u.optin_edu_date', 's.time_new'), $sql);
		//			$site_data_lead5media_Array = $this->_connection->query($sql, $vars);
		//			//get lead5media data from array $site_data_lead5media_Array
		//			foreach($site_data_lead5media_Array as $k=>$site_data){
		//				$adreport['lead5media'][$site_data['date']] = array( 'placement' => 'opt-in',
		//																	 't' => $site_data['date'],
		//																	 'site' => $site_data['site'],
		//																	 'advertiser' => 'lead5media',
		//																	 'impression' => $site_data['impression'],
		//																	 'revenue' => $site_data['conversion'] *1
		//																	);
		//			}
		//			foreach($lead5media_conversion as $k=>$v){
		//				if(!empty($adreport['lead5media'][$v['date']])){
		//					$adreport['lead5media'][$v['date']]['conversion'] = $v['conversion'];
		//				}
		//			}
					
					//get site_data relative to offerwall, array in $ad_data_offerwall_Array
					$sql = " SELECT {$dateFormat}, site, offer_wall1 AS advertiser, SUM(s.morecredit) AS impression, SUM(s.reward) AS conversion FROM `$tbName` AS s " . $sqlwhere . " AND morecredit>0 AND site!='' AND offer_wall1!='' GROUP BY date, site ";
					$sql = str_replace('%1$s', 'time_new', $sql);
					$ad_data_offerwall_Arrays['1'] = $this->_connection->query($sql, $vars);
					
					$sql = " SELECT {$dateFormat}, site, offer_wall2 AS advertiser, SUM(s.morecredit) AS impression, SUM(s.reward) AS conversion FROM `$tbName` AS s " . $sqlwhere . " AND morecredit>0 AND site!='' AND offer_wall2!='' GROUP BY date, site ";
					$sql = str_replace('%1$s', 'time_new', $sql);
					$ad_data_offerwall_Arrays['2'] = $this->_connection->query($sql, $vars);
					
					$sql = " SELECT {$dateFormat}, site, offer_wall3 AS advertiser, SUM(s.morecredit) AS impression, SUM(s.reward) AS conversion FROM `$tbName` AS s " . $sqlwhere . " AND morecredit>0 AND site!='' AND offer_wall3!='' GROUP BY date, site ";
					$sql = str_replace('%1$s', 'time_new', $sql);
					$ad_data_offerwall_Arrays['3'] = $this->_connection->query($sql, $vars);
					
					$sql = " SELECT {$dateFormat}, site, offer_wall4 AS advertiser, SUM(s.morecredit) AS impression, SUM(s.reward) AS conversion FROM `$tbName` AS s " . $sqlwhere . " AND morecredit>0 AND site!='' AND offer_wall4!='' GROUP BY date, site ";
					$sql = str_replace('%1$s', 'time_new', $sql);
					$ad_data_offerwall_Arrays['4'] = $this->_connection->query($sql, $vars);
					
					//get offerwall advertiser of each site, array in $sites_advertiser
					$sql = " SELECT id, name FROM monetizations ";
					$result = $this->_connection->query($sql);
					$advertisers = Array();
					foreach($result as $a){
						$advertisers[$a['id']] = $a['name'];
					}
					$sql = " SELECT domain, monetization1, monetization2, monetization3, monetization4 FROM sites ";
					$result = $this->_connection->query($sql);
					$sites_advertiser = Array();
					foreach($result as $s){
						if($s['monetization1'] != ''){ $sites_advertiser[$s['domain']][] = $advertisers[$s['monetization1']]; }
						if($s['monetization2'] != ''){ $sites_advertiser[$s['domain']][] = $advertisers[$s['monetization2']]; }
						if($s['monetization3'] != ''){ $sites_advertiser[$s['domain']][] = $advertisers[$s['monetization3']]; }
						if($s['monetization4'] != ''){ $sites_advertiser[$s['domain']][] = $advertisers[$s['monetization4']]; }
					}
					
					//get offerwall data from array $ad_data_offerwall_Array, array $ad_data_Array and array $sites_advertiser
					foreach($ad_data_offerwall_Arrays as $offerId=>$ad_data_offerwall_Array){
						foreach($ad_data_offerwall_Array as $ad_data_offerwall){
							$site = $ad_data_offerwall['site'];
							foreach( $sites_advertiser[$site] as $k=>$ad ){
								if($ad != '' && $ad != null && $ad == $ad_data_offerwall['advertiser']){
									$revenue = $ad_data_Array[ $ad_data_offerwall['date'] . ' ' . $site . ' ' . $ad ]['revenue'];
									$click = $ad_data_Array[ $ad_data_offerwall['date'] . ' ' . $site . ' ' . $ad ]['click'];
									$adreport['offer wall' . " #$offerId"][] =array('placement' => 'offer wall' . " #$offerId",
																			  't' => $ad_data_offerwall['date'],
																			  'site' => $site,
																			  'advertiser' => $ad,
																			  'conversion' => $ad_data_offerwall['conversion'],
																			  'impression' => $ad_data_offerwall['impression'],
																			  'revenue' => $revenue,
																			  'click' => $click 
																			);
								}
							}
						}
					}
		
					$finalResult = array_merge($adreport, $finalResult);
				}
				catch (Exception $e){
					QLog::log(__CLASS__.' '.__METHOD__.' --- '.$e->getMessage(), QLog::ERR);
					throw new Model_Exception($e->getMessage(), $e->getCode());
				}
			}
			return $finalResult;
		}
		return false;
	}
	
	public function getEmailReport($duration, $customrangeDate=array(), $interval, $dispCol=array()){
		$this->_init($duration, $customrangeDate);
		$vars = array();
		$query = array();
		if(!empty($this->_start)){
			$query[] = '%1$s>=? ';
			$vars[] = $this->_start;
		}
		if(!empty($this->_end)){
			$query[] = '%1$s<?';
			$vars[] = $this->_end;
		}
		if(!empty($query)){
			$sqlwhere = ' WHERE ' . implode(' AND ', $query);
			//$sqlwhere = ' WHERE 1 ';
		}
			
		$dateFormat = $this->getDateFormat($interval, '%1$s');
		$sqlStart = " SELECT {$dateFormat}, ";
		$sqlgroup = ' GROUP BY date, email_type  ';
		if(isset($dispCol[7])){
			$sqlStart .= ' sender, ';
			$sqlgroup .= ' ,sender ';
		}
		
		try{
			$uc_array = array();
			$open_array = array();
			$sent_array = array();
			
			
			//get unique_click and group by time ,array in $uc_array
			$sql = $sqlStart . " COUNT(*) AS ucNum, email_type AS type FROM emails " . $sqlwhere . " AND (click1 + click2 + click3 + click4 + click5 + click6 + click7 + click8 + click9 + click10)>0 " . $sqlgroup;
			$sql = str_replace('%1$s', '`sent_date`', $sql);
			$result = $this->_connection->query($sql, $vars);
			foreach($result as $k=>$v){
				$uc_array[ $v['date'] . ' ' . $v['sender'] . ' ' . $v['type'] ] += $v['ucNum'];
			}
			
			//get open
			$sql = $sqlStart . " COUNT(*) AS openNum, email_type AS type FROM emails " . $sqlwhere.' AND open_time_date>sent_date ' . $sqlgroup;
			$sql = str_replace('%1$s', 'sent_date', $sql);
			$result = $this->_connection->query($sql, $vars);
			foreach($result as $k=>$v){
				$open_array[ $v['date'] . ' ' . $v['sender'] . ' ' . $v['type'] ] += $v['openNum'];
			}
			
			//get sent
			$sql = $sqlStart . " COUNT(*) AS sentNum, email_type AS type FROM emails " . $sqlwhere . $sqlgroup;
			$sql = str_replace('%1$s', 'sent_date', $sql);
			$result = $this->_connection->query($sql, $vars);
			foreach($result as $k=>$v){
				$sent_array[ $v['date'] . ' ' . $v['sender'] . ' ' . $v['type'] ] += $v['sentNum'];	
			}
			
			//merge $sent_array, $open_array, $uc_array
			$emaillist = array();
			foreach($sent_array as $k=>$sent){
				list($date, $site, $type) = explode(' ', $k);
				$emaillist[] = array(
					't' => $date,
					'site' => $site,
					'sent' => $sent,
					'unique_click' => $uc_array[$k],
					'open' => $open_array[$k],
					'type' => $type
				);
			}

			return $emaillist;
		}catch (Exception $e){
			QLog::log(__CLASS__.' '.__METHOD__.' --- '.$e->getMessage(), QLog::ERR);
			throw new Model_Exception($e->getMessage(), $e->getCode());
		}
	}
	
	public function getOfferOfWeekReport($duration, $customrangeDate=array(), $interval, $dispCol=array()){
		$this->_init($duration, $customrangeDate);
		$vars = array();
		$query = array();
		if(!empty($this->_start)){
			$query[] = '%1$s>=? ';
			$vars[] = $this->_start;
		}
		if(!empty($this->_end)){
			$query[] = '%1$s<?';
			$vars[] = $this->_end;
		}		
		
		//add email type filter
		$query[] = 'email_type=?';
		$vars[] = 'offersofweek';
		
		if(!empty($query)){
			$sqlwhere = ' WHERE ' . implode(' AND ', $query);
		}
		
		$dateFormat = $this->getDateFormat($interval, '%1$s');
		
		$tbNames = $this->getTbByDuration();	dump($tbNames);
		if(!empty($tbNames)){
			$finalResult = array();
			foreach($tbNames as $tbName){
				try{
					$sql = " SELECT " . $dateFormat . ", SUM(email_sent) AS email_sent, SUM(email_open) AS email_open, SUM(email_click) AS email_click, SUM(email_offer_complete) AS email_offer_complete FROM $tbName " . $sqlwhere . " GROUP BY date " ;
					$sql = str_replace('%1$s', 'email_offer_tosentdate', $sql);
					$offersReport = $this->_connection->query($sql, $vars);
					
					$finalResult = array_merge($offersReport, $finalResult);
				}catch (Exception $e){
					QLog::log(__CLASS__.' '.__METHOD__.' --- '.$e->getMessage(), QLog::ERR);
					throw new Model_Exception($e->getMessage(), $e->getCode());
				}
			}
			return $finalResult;
		}
		return false;
	}
	
	public function getBluebarOfferReport($duration, $customrangeDate=array(), $interval, $dispCol=array()){
		$this->_init($duration, $customrangeDate);
		$vars = array();
		$query = array();
		if(!empty($this->_start)){
			$query[] = '%1$s>=? ';
			$vars[] = $this->_start;
		}
		if(!empty($this->_end)){
			$query[] = '%1$s<?';
			$vars[] = $this->_end;
		}
		
		if(!empty($query)){
			$sqlwhere = ' WHERE ' . implode(' AND ', $query);
		}
		$sqlgroup = ' GROUP BY date, site, s.bluebar_offer ';
		
		$dateFormat = $this->getDateFormat($interval, '%1$s');
	
		$tbNames = $this->getTbByDuration();	dump($tbNames);
		if(!empty($tbNames)){
			$finalResult = array();
			foreach($tbNames as $tbName){
				try{
					//get click num
					$sql = ' SELECT ' . $dateFormat . ", site, b.is_partial, b.offer_text, SUM(bluebar_click) AS click, SUM(bluebar_complete) AS complete FROM $tbName s LEFT JOIN bluebar_offer b ON s.bluebar_offer = b.id " . $sqlwhere . ' AND s.bluebar_offer!="" ' . $sqlgroup;
					$sql = str_replace('%1$s', 'time_new', $sql);
					$bluebarOfferReport = $this->_connection->query($sql, $vars);
					
					$finalResult = array_merge($bluebarOfferReport, $finalResult);
				}catch (Exception $e){
					QLog::log(__CLASS__.' '.__METHOD__.' --- '.$e->getMessage(), QLog::ERR);
					throw new Model_Exception($e->getMessage(), $e->getCode());
				}
			}
			return $finalResult;
		}
		return false;
	}
	
	public function getBannerOfferReport($duration, $customrangeDate=array(), $interval, $dispCol=array()){
		$this->_init($duration, $customrangeDate);
		$vars = array();
		$query = array();
		if(!empty($this->_start)){
			$query[] = '%1$s>=? ';
			$vars[] = $this->_start;
		}
		if(!empty($this->_end)){
			$query[] = '%1$s<?';
			$vars[] = $this->_end;
		}
		
		if(!empty($query)){
			$sqlwhere = ' WHERE ' . implode(' AND ', $query);
		}
		$sqlgroup = ' GROUP BY date, site, s.banner_offer ';
		
		$dateFormat = $this->getDateFormat($interval, '%1$s');
	
		$tbNames = $this->getTbByDuration();	dump($tbNames);
		if(!empty($tbNames)){
			$finalResult = array();
			foreach($tbNames as $tbName){
				try{
					//get click num
					$sql = ' SELECT ' . $dateFormat . ", site, b.banner, b.is_partial, b.offer_name, SUM(banner_click) AS click, SUM(s.impression) AS impression, SUM(banner_complete) AS conversion FROM $tbName s LEFT JOIN banner_offers b ON s.banner_offer = b.id " . $sqlwhere . ' AND s.banner_offer!="" ' . $sqlgroup;
					$sql = str_replace('%1$s', 'time_new', $sql);
					$bannerOfferReport = $this->_connection->query($sql, $vars);
					
					$finalResult = array_merge($bannerOfferReport, $finalResult);
				}catch (Exception $e){
					QLog::log(__CLASS__.' '.__METHOD__.' --- '.$e->getMessage(), QLog::ERR);
					throw new Model_Exception($e->getMessage(), $e->getCode());
				}
			}
			return $finalResult;
		}
		return false;
	}
	
	public function getDailyDealsOfferReport($duration, $customrangeDate=array(), $interval, $dispCol=array()){
		$this->_init($duration, $customrangeDate);
		$vars = array();
		$query = array();
		if(!empty($this->_start)){
			$query[] = '%1$s>=? ';
			$vars[] = $this->_start;
		}
		if(!empty($this->_end)){
			$query[] = '%1$s<?';
			$vars[] = $this->_end;
		}
		
		if(!empty($query)){
			$sqlwhere = ' WHERE ' . implode(' AND ', $query);
		}
		$sqlgroup = ' GROUP BY date, site, s.dailydeals_offer ';
		
		$dateFormat = $this->getDateFormat($interval, '%1$s');
	
		$tbNames = $this->getTbByDuration();	dump($tbNames);
		if(!empty($tbNames)){
			$finalResult = array();
			foreach($tbNames as $tbName){
				try{
					//get click num
					$sql = ' SELECT ' . $dateFormat . ", site, d.describe_text, SUM(dailydeals_click) AS click, SUM(dailydeals_complete) AS complete FROM $tbName s LEFT JOIN dailydeals_offer d ON s.dailydeals_offer = d.id " . $sqlwhere . ' AND s.dailydeals_offer!="" ' . $sqlgroup;
					$sql = str_replace('%1$s', 'time_new', $sql);dump($sql, $vars);
					$dailyDealsOfferReport = $this->_connection->query($sql, $vars);
					
					$finalResult = array_merge($dailyDealsOfferReport, $finalResult);
				}catch (Exception $e){
					QLog::log(__CLASS__.' '.__METHOD__.' --- '.$e->getMessage(), QLog::ERR);
					throw new Model_Exception($e->getMessage(), $e->getCode());
				}
			}
			return $finalResult;
		}
		return false;
	}
	
	public function getOptinReport($duration, $customrangeDate=array(), $interval, $dispCol=array()){
		$this->_init($duration, $customrangeDate);
		$cols=array('buy_type', 'media_type', 'pub', 'pla', 'cam', 'cre', 'monetization', 'site');
		$vars = array();
		$query = array();
		if(!empty($this->_start)){
			$query[] = '%1$s>=? ';
			$vars[] = $this->_start;
		}
		if(!empty($this->_end)){
			$query[] = '%1$s<?';
			$vars[] = $this->_end;
		}
		
		if(!empty($query)){
			$sqlwhere = ' WHERE ' . implode(' AND ', $query);
		}
		$sqlwhere .= ' AND status = "sent" ';
		
		$sqlgroup = ' GROUP BY date, u.site_id ';
		$dateFormat = $this->getDateFormat($interval, '%1$s');
		$sqlStart = "SELECT {$dateFormat}, ";
		foreach($dispCol as $col){
			$sqlgroup .= (', '.$cols[$col]);
			$sqlStart .= ($cols[$col].', ');
		}
		
		
		try{
			$optinReport = array();
			
			$sql = $sqlStart . ' s.domain, u.cam, u.pub, u.pla, "shoppingjobs" AS partner, COUNT(*) AS successed FROM shoppingjobs_optin LEFT JOIN users u ON shoppingjobs_optin.uid = u.id LEFT JOIN sites s ON u.site_id = s.id ' . $sqlwhere . $sqlgroup;
			$sql = str_replace('%1$s', 'send_date', $sql);
			$shoppingJobsReport = $this->_connection->query($sql, $vars);
			
			$sql = $sqlStart . ' s.domain, u.cam, u.pub, u.pla, "inboxpay" AS partner, COUNT(*) AS successed FROM inpays_optin LEFT JOIN users u ON inpays_optin.uid = u.id LEFT JOIN sites s ON u.site_id = s.id ' . $sqlwhere . $sqlgroup;
			$sql = str_replace('%1$s', 'send_date', $sql);
			$inpaysReport = $this->_connection->query($sql, $vars);
			
			$sql = $sqlStart . ' s.domain, u.cam, u.pub, u.pla, "eversave " AS partner, COUNT(*) AS successed FROM eversave_optin LEFT JOIN users u ON eversave_optin.uid = u.id LEFT JOIN sites s ON u.site_id = s.id ' . $sqlwhere . $sqlgroup;
			$sql = str_replace('%1$s', 'send_date', $sql);
			$eversaveReport = $this->_connection->query($sql, $vars);
			
			$sql = $sqlStart . ' s.domain, u.cam, u.pub, u.pla, "getamerican " AS partner, COUNT(*) AS successed FROM greateamerican_optin LEFT JOIN users u ON greateamerican_optin.uid = u.id LEFT JOIN sites s ON u.site_id = s.id ' . $sqlwhere . $sqlgroup;
			$sql = str_replace('%1$s', 'send_date', $sql);
			$getamericanReport = $this->_connection->query($sql, $vars);
			
			$optinReport = array_merge($shoppingJobsReport, $inpaysReport, $eversaveReport, $getamericanReport);
			
			return $optinReport;
		}catch (Exception $e){
			QLog::log(__CLASS__.' '.__METHOD__.' --- '.$e->getMessage(), QLog::ERR);
			throw new Model_Exception($e->getMessage(), $e->getCode());
		}
	}

	public function getCol($col, $params){
		$sql = "SELECT distinct `".$col."` FROM `site_data` ";
			
		$vars = array();
		$query = array();
		foreach($params as $k=>$param){
			$query[] = '`'.$k.'`=? ';
			$vars[] = $param;
		}
			
		if(!empty($query)){
			$sql .= ' where ' . implode(' and ', $query);
		}
		try
		{
			$result = $this->_connection->query($sql, $vars);
			return $result;
		}
		catch (Exception $e)
		{
			QLog::log(__CLASS__.' '.__METHOD__.' --- '.$e->getMessage(), QLog::ERR);
			throw new Model_Exception($e->getMessage(), $e->getCode());
		}
	}

//	private function groupBy_Cols($result, $groupbyCols=array(), $sumCols=array())
//	{
//		$records=array();
//		foreach($result as $k=>$r){
//			$allflag=false;
//			foreach($records as $kk=>$rr){
//				$colsflag=true;
//				foreach($groupbyCols as $groupbyCol){
//					if($r[$groupbyCol] != $rr[$groupbyCol]){
//						$colsflag=false;
//						break;
//					}
//				}
//				if($colsflag){
//					foreach($sumCols as $sumCol){
//						$records[$kk][$sumCol] = $records[$kk][$sumCol]*1 + $r[$sumCol]*1;
//					}
//
//					$allflag=true;
//					break;
//				}
//			}
//			if(!$allflag){
//				$records[]=$r;
//			}
//		}
//		return $records;
//	}
	private function groupBy_Cols($data, $groupbyCols=array(), $sumCols=array(), $doCount=false, $countAsName='count')
	{
		$result=array();
		foreach ($data as $d)
		{
			$keyArr = array();
			foreach($groupbyCols as $g){
				$keyArr[] = $d[$g];
			}
			
			$key = implode(":", $keyArr);
			if (empty($result[$key]))
			{
				$result[$key] = array();
				$result[$key] = $d;
				if($doCount){
					$result[$key][$countAsName] = 1;
				}
			}
			else
			{
				foreach($d as $k=>$v){
					if(in_array($k, $sumCols)){
						$result[$key][$k] += $d[$k];
					}else{
						$result[$key][$k] = $d[$k];
					}
				}
				$result[$key][$countAsName]++;
			}
		}
		
		return $result;
	}

	private function formatResulttime(&$result, $interval, $time_key = 't'){
		foreach($result as $k=>$r){
			$result[$k][$time_key] = strtotime($result[$k][$time_key], time());
			switch($interval){
				case 1:
					$result[$k][$time_key] = date('m/d/Y', $result[$k][$time_key]);
					break;
				case 2:
					$result[$k][$time_key] = date('w/Y-m-d', $result[$k][$time_key]);
					$result[$k][$time_key] = Util::weekBorder($result[$k][$time_key], 'start');
					break;
				case 3:
					$result[$k][$time_key] = date('m/01/Y', $result[$k][$time_key]);
					break;
				default:
					$result[$k][$time_key] = date('m/d/Y H:00:00', $result[$k][$time_key]);
					break;
			}
		}
	}
	
	private function usingDateAsDbResultKey($sqlResult) {
		$temp = array();
		if (count($sqlResult) > 0) {
			foreach ($sqlResult as $resultItem) {
				foreach ($resultItem as $k => $v) {
					if ('date' == $k) {
						unset($resultItem['date']);
						$temp[$v] = $resultItem;
					}
				}
			}
		}
		return $temp;
	}
	
	private function mergeDbResultByDate($mergedReuslt, &$toBeMergedReuslt) {
		if(count($mergedReuslt) == 0) {
			return $toBeMergedReuslt;
		}
		foreach ($mergedReuslt as $date => $reusltItem) {
			if(isset($toBeMergedReuslt[$date])) {
				$mergedReuslt[$date] = array_merge($mergedReuslt[$date], $toBeMergedReuslt[$date]);
				unset($toBeMergedReuslt[$date]);
			}
		}
		$mergedReuslt = $mergedReuslt + $toBeMergedReuslt;
		return $mergedReuslt;
	}
	
	private function getTbByDuration()
	{
		$tbNames = array();
		$startTime = strtotime($this->_start);
		$endTime = strtotime($this->_end);
		do{
			$tbNames[] = 'site_data_' . date('Y_m', $startTime);
			$startTime = strtotime("+1 month", $startTime);
		}while (strtotime(date('Y-m', $startTime)) <= strtotime(date('Y-m', $endTime)));
		
		$tbNames = array_unique($tbNames);
		
		return $tbNames;
	}
	
}






